alter table bidefault.tmp_cainiao_active_tn_history_dt set tblproperties (' external.table.purge '=' true ');
drop table bidefault.tmp_cainiao_active_tn_history_dt ;
create external table bidefault.tmp_cainiao_active_tn_history_dt (
   taking_code string comment '揽收网点',
   taking_name string comment '' ,
   dispatch_code string comment '派件网点',
   dispatch_name string comment '' ,
   start_hm string comment '开始时段',
   end_hm string comment '开始时段',
   line_str string comment '线路code',
   aging_days int comment '时效天数',
   line_cnt int  comment '(始末网点,揽收时段,线路,时效,线路)单量'
   aging_days_cnt int  comment '(始末网点,揽收时段,时效)单量'
   taking_hm_cnt  int  comment '(始末网点,揽收时段)单量'
   se_network_total int comment '始末网点单量',
   bill_total bigint comment '基数总单量',
   sort_number int comment '排序' ,
   update_time timestamp comment '更新时间' 
) comment '菜鸟动态时效_历史线路单量'
partitioned by (dt string comment '时间分区')
stored as parquet
location '/dw/hive/bidefault.db/external/tmp_cainiao_active_tn_history_dt'
tblproperties (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);

alter table bidefault.tmp_cainiao_active_tn_history_dt add columns( is_within_shift tinyint comment'1班次内0班次外')CASCADE;


create external table bidefault.tmp_cainiao_active_tn_match_dt (
   taking_code string comment '揽收网点code',
   taking_name string comment '揽收网点name',
   deliver_code string comment '派件网点code',
   deliver_name string comment '派件网点name',
   start_hm string comment '开始时段',
   end_hm string comment '开始时段',
   line_str string comment '线路code',
   aging_days int comment '时效天数',
   out_sign_days int comment '静态时效天',
   is_within_shift tinyint comment '静态路由id明细 ',
   detail_route_id string comment '中心线路_动态',
   center_line_s string comment '中心线路_静态',
   is_main_route int comment '是否主用路由 1 3主用0备用',
   route_id string comment '路由 id ',
   weight int comment '线路排序权重',
   line_rn string comment '线路排序' 
) COMMENT '动态和静态匹配中间表'
PARTITIONED BY (dt STRING COMMENT '日期分区')
STORED AS PARQUET
LOCATION '/dw/hive/bidefault.db/external/tmp_cainiao_active_tn_match_dt'
TBLPROPERTIES (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);

alter table bidefault.tmp_cainiao_active_tn_match_dt change column center_line_d  detail_route_id string comment'静态路由id明细' CASCADE;
alter table bidefault.tmp_cainiao_active_tn_match_dt change columns( center_line_d  detail_route_id string comment'静态路由id明细')CASCADE;


create external table bidefault.dm_cainiao_active_tn_all_dt (
   taking_code string comment '揽收网点code' ,
   taking_name string comment '揽收网点name' ,
   deliver_code string comment '派件网点code' ,
   deliver_name string comment '派件网点name' ,
   start_hm string comment '开始时段' ,
   end_hm string comment '结束时段' ,
   detail_route_id  string comment '' ,
   route_id  string comment '' 
) COMMENT '菜鸟动态时效结果'
PARTITIONED BY (dt STRING COMMENT '日期分区')
STORED AS PARQUET
LOCATION '/dw/hive/bidefault.db/external/dm_cainiao_active_tn_all_dt'
TBLPROPERTIES (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);

alter table bidefault.dm_cainiao_actual_network_tn_dt add columns(
    route_id string comment'路由明细id'
    ,is_bushu tinyint comment'1时段补,2网点无发货补'
    ,detail_route_id string comment'路由明细id'
    )CASCADE;
